const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
  host: '43.143.253.188',
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4',
  port: 3306,
  connectTimeout: 60000
};

async function checkTableColumns (connection, tableName) {
  try {
    // 获取表的所有列信息
    const [columns] = await connection.execute(
      `SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
       FROM information_schema.columns 
       WHERE table_schema = ? AND table_name = ?
       ORDER BY ORDINAL_POSITION`,
      [dbConfig.database, tableName]
    );

    const result = {
      tableName,
      hasCreatedAt: false,
      hasUpdatedAt: false,
      hasCreated_at: false,
      hasUpdated_at: false,
      columns: columns.map(col => ({
        name: col.COLUMN_NAME,
        type: col.DATA_TYPE,
        nullable: col.IS_NULLABLE,
        default: col.COLUMN_DEFAULT,
        extra: col.EXTRA,
        comment: col.COLUMN_COMMENT
      }))
    };

    // 检查是否存在驼峰命名的字段
    result.hasCreatedAt = columns.some(col => col.COLUMN_NAME === 'createdAt');
    result.hasUpdatedAt = columns.some(col => col.COLUMN_NAME === 'updatedAt');

    // 检查是否存在下划线命名的字段
    result.hasCreated_at = columns.some(col => col.COLUMN_NAME === 'created_at');
    result.hasUpdated_at = columns.some(col => col.COLUMN_NAME === 'updated_at');

    return result;
  } catch (error) {
    console.error(`检查表 ${tableName} 时出错:`, error.message);
    return null;
  }
}

async function getAllTables (connection) {
  try {
    const [tables] = await connection.execute(
      `SELECT TABLE_NAME 
       FROM information_schema.tables 
       WHERE table_schema = ? AND table_type = 'BASE TABLE'
       ORDER BY TABLE_NAME`,
      [dbConfig.database]
    );
    return tables.map(row => row.TABLE_NAME);
  } catch (error) {
    console.error('获取表列表时出错:', error.message);
    return [];
  }
}

async function main () {
  console.log('🔍 开始检查数据库字段命名...');
  console.log(`📋 目标数据库: ${dbConfig.host}/${dbConfig.database}`);

  let connection;

  try {
    // 连接数据库
    console.log('\n🔌 连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');

    // 获取所有表
    console.log('\n📊 获取数据库表列表...');
    const tables = await getAllTables(connection);
    console.log(`✅ 找到 ${tables.length} 个表`);

    // 检查每个表
    console.log('\n🔍 开始检查各表字段...');

    const results = [];
    let needsMigration = 0;
    let alreadyMigrated = 0;
    let noTimestampFields = 0;

    for (const tableName of tables) {
      const result = await checkTableColumns(connection, tableName);
      if (result) {
        results.push(result);

        // 分析结果
        const hasOldFields = result.hasCreatedAt || result.hasUpdatedAt;
        const hasNewFields = result.hasCreated_at || result.hasUpdated_at;
        const hasAnyTimestamp = hasOldFields || hasNewFields;

        if (!hasAnyTimestamp) {
          noTimestampFields++;
          console.log(`⏭️  ${tableName}: 无时间戳字段`);
        } else if (hasOldFields && !hasNewFields) {
          needsMigration++;
          console.log(`❌ ${tableName}: 需要迁移 (有驼峰字段，无下划线字段)`);
        } else if (!hasOldFields && hasNewFields) {
          alreadyMigrated++;
          console.log(`✅ ${tableName}: 已迁移 (只有下划线字段)`);
        } else if (hasOldFields && hasNewFields) {
          needsMigration++;
          console.log(`⚠️  ${tableName}: 混合状态 (同时有驼峰和下划线字段)`);
        }
      }
    }

    // 输出详细统计
    console.log('\n📊 检查结果统计:');
    console.log(`   总表数: ${tables.length}`);
    console.log(`   已迁移: ${alreadyMigrated}`);
    console.log(`   需要迁移: ${needsMigration}`);
    console.log(`   无时间戳字段: ${noTimestampFields}`);

    // 输出需要迁移的表详情
    if (needsMigration > 0) {
      console.log('\n❌ 需要迁移的表:');
      results.forEach(result => {
        const hasOldFields = result.hasCreatedAt || result.hasUpdatedAt;
        const hasNewFields = result.hasCreated_at || result.hasUpdated_at;

        if (hasOldFields && !hasNewFields) {
          console.log(`   ${result.tableName}:`);
          if (result.hasCreatedAt) console.log(`     - 有 createdAt 字段`);
          if (result.hasUpdatedAt) console.log(`     - 有 updatedAt 字段`);
        } else if (hasOldFields && hasNewFields) {
          console.log(`   ${result.tableName}: 混合状态`);
          if (result.hasCreatedAt) console.log(`     - 有 createdAt 字段`);
          if (result.hasUpdatedAt) console.log(`     - 有 updatedAt 字段`);
          if (result.hasCreated_at) console.log(`     - 有 created_at 字段`);
          if (result.hasUpdated_at) console.log(`     - 有 updated_at 字段`);
        }
      });
    }

    // 输出已迁移的表
    if (alreadyMigrated > 0) {
      console.log('\n✅ 已迁移的表:');
      results.forEach(result => {
        const hasOldFields = result.hasCreatedAt || result.hasUpdatedAt;
        const hasNewFields = result.hasCreated_at || result.hasUpdated_at;

        if (!hasOldFields && hasNewFields) {
          console.log(`   ${result.tableName}:`);
          if (result.hasCreated_at) console.log(`     - 有 created_at 字段`);
          if (result.hasUpdated_at) console.log(`     - 有 updated_at 字段`);
        }
      });
    }

    // 总结
    if (needsMigration === 0) {
      console.log('\n🎉 所有表都已成功迁移为下划线命名！');
    } else {
      console.log(`\n⚠️  还有 ${needsMigration} 个表需要迁移`);
    }

  } catch (error) {
    console.error('\n❌ 检查过程中发生错误:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 执行主函数
main().catch(console.error);

